package com.ezops.util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.Reader;
import java.io.StringWriter;
import java.io.Writer;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVRecord;
import org.apache.commons.io.input.BOMInputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 
 * @author vgaurav
 * 
 */
public class Report implements Iterable<Report.Row> {
	private static final Logger logger = LoggerFactory.getLogger(Report.class);

	public static final String STYLE_GOOD = "good";
	public static final String STYLE_BAD = "bad";
	public static final String STYLE_HEADER = "header";
	public static final String STYLE_NEUTRAL = "neutral";
	public static final String STYLE_LIGHT_BLUE = "lightblue";
	public static final String STYLE_DARK_BLUE = "darkblue";

	public static enum Format {
		xlsx, txt
	}

	public class Row {
		private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
		private TreeMap<Integer, String> values;
		private Map<Integer, String> cellStyles = new HashMap<>();
		private String rowStyle;
		private String rowId;

		public Row() {
			values = new TreeMap<>();
		}

		public void setId(String id) {
			rowId = id;
		}

		public String getId() {
			return rowId;
		}

		@Override
		public String toString() {
			StringBuilder buf = new StringBuilder();
			int numColumns = values.isEmpty() ? 0 : values.lastKey();
			for (int i = 0; i <= numColumns; i++) {
				if (i > 0)
					buf.append('\t');
				String val = values.get(i);
				buf.append(val == null ? "-" : val);
			}
			return buf.toString();
		}

		public void setValue(int columnIndex, String val) {
			values.put(columnIndex, val);

			Map<String, Row> rowMap = rowMaps.get(columnIndex);
			if (rowMap != null)
				rowMap.put(val, this);

			isModified = true;
		}

		public void setCellStyle(int columnIndex, String style) {
			cellStyles.put(columnIndex, style);
		}

		public String getCellStyle(int columnIndex) {
			return cellStyles.get(columnIndex);
		}

		public void setValue(int columnIndex, Date val) {
			values.put(columnIndex, val == null ? null : dateFormat.format(val));
		}

		public String getValue(int columnIndex) {
			return values.get(columnIndex);
		}

		public void setRowStyle(String styleName) {
			rowStyle = styleName;
		}

		public String getRowStyle() {
			return rowStyle;
		}

		public Map<Integer, String> getValues() {
			return values;
		}
	}

	private List<Row> rows = new ArrayList<>();
	private Map<Integer, Map<String, Row>> rowMaps = new HashMap<>();
	private String[] columns = {};
	Map<String, CellStyle> styleMap = new HashMap<>();
	boolean isModified = false;
	int groupColumnIndex = -1;
	int autoresize = 0;

	private int MAX_STRING_LENGTH = 32767;

	public Report() {

	}

	public Report(File file) throws IOException {
		load(file);
	}

	public Report(InputStream is) throws IOException {
		loadXls(is, null);
	}

	public Report(Reader is) throws IOException {
		loadTxt(new BufferedReader(is));
	}

	public void clear() {
		rows.clear();
		rowMaps.clear();
	}

	public Iterator<Row> iterator() {
		return rows.iterator();
	}

	public Row createRow() {
		Row row = new Row();
		add(row);
		return row;
	}

	public Row createRow(Row src) {
		Row row = createRow();
		row.values = (TreeMap<Integer, String>) src.values.clone();
		row.cellStyles = src.cellStyles;
		row.rowStyle = src.rowStyle;

		return row;
	}

	public Row lastRow() {
		return rows.get(rows.size() - 1);
	}

	public void add(Row row) {
		rows.add(row);
	}

	public void setColumns(String[] cols) {
		columns = cols;
	}

	public String[] getColumns() {
		return columns;
	}

	public Row lookup(int columnIndex, String key) {
		return lookup(columnIndex, key, true);
	}

	public boolean deleteRow(Row row) {
		return rows.remove(row);
	}

	public Row lookup(int columnIndex, String key, boolean caseSensitive) {
		int cacheIndex = caseSensitive ? columnIndex : -columnIndex;
		Map<String, Row> rowMap = rowMaps.get(cacheIndex);
		if (rowMap == null) {
			rowMap = new HashMap<>();
			for (Row e : rows) {
				String v = e.getValue(columnIndex);
				if (!caseSensitive && v != null)
					v = v.toLowerCase();
				if (!rowMap.containsKey(v))
					rowMap.put(v, e);
			}
			rowMaps.put(cacheIndex, rowMap);
		}
		return rowMap.get(caseSensitive ? key : key.toLowerCase());
	}

	public Row getById(String key) {
		Map<String, Row> rowMap = rowMaps.get(-1);
		if (rowMap == null) {
			rowMap = new HashMap<>();
			for (Row e : rows) {
				String v = e.getId();
				if (!rowMap.containsKey(v))
					rowMap.put(v, e);
			}
			rowMaps.put(-1, rowMap);
		}
		return rowMap.get(key);
	}

	public void save(String fileName) throws IOException {
		File f = new File(fileName);

		if (f.getParentFile() != null)
			f.getParentFile().mkdirs();
		// files checked out from TFS are readonly. ensure user can write in
		// ./etc
		if (f.getParentFile() != null && !f.getParentFile().canWrite())
			throw new Error("Cannot write to " + f.getParentFile()
					+ ". Aborting .... ");

		if (fileName.endsWith(".xlsx")) {
			saveXls(fileName);
		} else {
			try (Writer fw = new OutputStreamWriter(new FileOutputStream(f),
					"UTF-8")) {
				write(fw);
			}
		}
		isModified = false;
	}

	public boolean isModified() {
		return isModified;
	}

	public void groupRows(int groupColumnIndex) {
		this.groupColumnIndex = groupColumnIndex;
	}

	public void autoresize(int rows) {
		autoresize = rows;
	}

	private CellStyle getStyle(Workbook wb, String styleName) {
		CellStyle style = styleMap.get(styleName);
		if (style == null) {
			style = wb.createCellStyle();
			// setStyleAttributes(styleName, style, wb);
			styleMap.put(styleName, style);
		}
		return style;
	}

	private void saveXls(String fileName) throws IOException {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		Sheet sheet = wb.createSheet();

		save(sheet);

		try (FileOutputStream out = new FileOutputStream(fileName)) {
			wb.write(out);
		}

		wb.dispose();
		styleMap.clear();
	}

	public void save(Sheet sheet) throws IOException {
		sheet.setRowSumsBelow(false);
		Workbook wb = sheet.getWorkbook();

		int rowNumber = 0;
		org.apache.poi.ss.usermodel.Row headerRow = sheet
				.createRow(rowNumber++);
		for (int i = 0; i < columns.length; i++) {
			Cell cell = headerRow.createCell(i);
			cell.setCellValue(columns[i]);
			String styleName = "header";
			CellStyle style = getStyle(wb, styleName);
			cell.setCellStyle(style);
		}

		int lastColumnIndex = 0;
		int lastReportRow = -1;
		for (Report.Row r : rows) {
			org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowNumber++);

			CellStyle rowStyle = null;
			String styleName = r.getRowStyle();
			if (styleName != null) {
				rowStyle = getStyle(wb, styleName);
			}
			int columnIndex;
			for (columnIndex = 0; columnIndex < Math.max(r.values.lastKey(),
					columns.length); columnIndex++) {
				Cell cell = row.createCell(columnIndex);
				String value = r.values.get(columnIndex);
				if (groupColumnIndex != -1 && columnIndex == 0 && value != null) {
					if (lastReportRow != -1
							&& (row.getRowNum() - lastReportRow) > 1) {
						sheet.groupRow(lastReportRow + 1, row.getRowNum() - 1);
					}
					lastReportRow = row.getRowNum();
				}

				if (value != null) {
					if (value.matches("\\d+") && value.length() < 15) {
						try {
							cell.setCellValue(Long.parseLong(value));
						} catch (Exception e) {
							logger.warn(e.getMessage());
							cell.setCellValue(value);
						}
					} else {
						if (value.length() > MAX_STRING_LENGTH)
							value = value.substring(0, MAX_STRING_LENGTH);
						cell.setCellValue(value);
					}

				}
				lastColumnIndex = Math.max(lastColumnIndex, columnIndex);

				CellStyle style = null;

				styleName = r.getCellStyle(columnIndex);
				if (styleName != null)
					style = getStyle(wb, styleName);
				if (style == null)
					style = rowStyle;

				if (style != null)
					cell.setCellStyle(style);
			}

			if (autoresize > 0 && (rowNumber % autoresize) == 0)
				autosize(sheet, lastColumnIndex);
		}
		if (groupColumnIndex != -1 && lastReportRow != -1
				&& (sheet.getLastRowNum() - lastReportRow) > 1) {
			sheet.groupRow(lastReportRow + 1, sheet.getLastRowNum());
		}

		autosize(sheet, lastColumnIndex);
		sheet.setAutoFilter(new CellRangeAddress(0, rowNumber, 0,
				lastColumnIndex));
	}

	void autosize(Sheet sheet, int lastColumnIndex) {
		for (int i = 0; i <= lastColumnIndex; i++) {
			try {
				sheet.autoSizeColumn(i);
			} catch (Exception e) {
				logger.warn("autoSizeColumn(" + i + ") failed: "
						+ e.getMessage());
			}
			int cw = sheet.getColumnWidth(i);
			// increase width to accommodate drop-down arrow in the header
			if (cw / 256 < 20)
				sheet.setColumnWidth(i, 256 * 12);
			else if (cw / 256 > 120)
				sheet.setColumnWidth(i, 256 * 120);
		}
	}

	public void load(File file) throws IOException {
		String fileName = file.getName();
		if (fileName.endsWith(".xlsx"))
			loadXls(file, null);
		else if (fileName.endsWith(".csv"))
			loadCsv(file);
		else
			loadTxt(file);
	}

	/**
	 * load from a tab-delimited file
	 */
	private void loadTxt(File file) throws IOException {
		try (BufferedReader reader = new BufferedReader(new InputStreamReader(
				new FileInputStream(file), "UTF-8"))) {
			loadTxt(reader);
		}
	}

	private void loadTxt(BufferedReader reader) throws IOException {
		clear();

		String line = reader.readLine();
		if (line != null)
			columns = line.split("\t");
		while ((line = reader.readLine()) != null) {
			String[] vals = line.split("\t");

			Row e = new Row();
			for (int i = 0; i < vals.length; i++) {
				String v = "-".equals(vals[i]) ? null : vals[i];
				e.values.put(i, v);
			}

			add(e);
		}
	}

	/**
	 * load from a .xlsx file
	 */
	public void loadXls(File file, String sheetName) throws IOException {
		try (FileInputStream is = new FileInputStream(file)) {
			loadXls(is, sheetName);
		}
	}

	public void loadXls(InputStream is, String sheetName) throws IOException {
		clear();

		Workbook wb = new XSSFWorkbook(is);
		Sheet sheet = sheetName == null ? wb.getSheetAt(0) : wb
				.getSheet(sheetName);
		if (sheet == null)
			throw new IllegalArgumentException("Invalid sheet name: "
					+ sheetName);
		DataFormatter df = new DataFormatter();
		boolean first = true;
		for (org.apache.poi.ss.usermodel.Row r : sheet) {
			if (first) {
				columns = new String[r.getLastCellNum() == -1 ? 0 : r
						.getLastCellNum()];
				for (Cell c : r)
					columns[c.getColumnIndex()] = df.formatCellValue(c);

			} else {
				Row e = new Row();
				for (Cell c : r) {
					e.values.put(c.getColumnIndex(), df.formatCellValue(c));
				}
				add(e);

			}
			first = false;
		}
	}

	/**
	 * load from .csv file
	 */
	private void loadCsv(File file) throws IOException {
		try (BufferedReader reader = new BufferedReader(new InputStreamReader(
				new FileInputStream(file), "UTF-8"))) {
			loadCsv(reader);
		}
	}

	private void loadCsv(BufferedReader reader) throws IOException {
		clear();

		String line = reader.readLine();
		if (line != null)
			columns = line.split(",");
		while ((line = reader.readLine()) != null) {
			String[] vals = line.split(",");

			Row e = new Row();
			for (int i = 0; i < vals.length; i++) {
				String v = "".equals(vals[i]) ? null : vals[i];
				e.values.put(i, v);
			}

			add(e);
		}
	}

	public void loadExcelCSV(File file) throws IOException {
		try (BOMInputStream reader = new BOMInputStream(
				new FileInputStream(file))) {
			InputStreamReader isr = new InputStreamReader(reader);
			Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(isr);
			Iterator<CSVRecord> itr = records.iterator();
			boolean isHeader = true;
			while (itr.hasNext()) {
				CSVRecord vals = itr.next();
				if (isHeader) {
					extractHeader(vals);
					isHeader = false;
				}
				else{
					Row e = new Row();
					for (int i = 0; i < vals.size(); i++) {
						String v = "".equals(vals.get(i)) ? null : vals.get(i)
								.replaceAll(",", "");
						e.values.put(i, v);
					}
					add(e);
				}
			}
		}
	}

	private void extractHeader(CSVRecord vals) {
		Iterator<String> headerItr = vals.iterator();
		List<String> columnHeaderList = new ArrayList<String>(vals.size());
		while(headerItr.hasNext()){
			columnHeaderList.add(headerItr.next());
		}
		this.columns = columnHeaderList.toArray(new String[vals.size()]);
	}

	@Override
	public String toString() {
		StringWriter sw = new StringWriter();
		try {
			write(sw);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		return sw.toString();
	}

	public void write(Writer out) throws IOException {
		PrintWriter fw = new PrintWriter(out);
		for (int i = 0; i < columns.length; i++) {
			if (i > 0)
				fw.write('\t');
			fw.write(columns[i]);
		}
		fw.println();

		for (Row e : rows) {
			fw.println(e.toString().replaceAll("\r?\n", ""));
		}
		out.flush();
	}

	public int size() {
		return rows.size();
	}

	public static void main(String[] args) throws IOException {

		Report r = new Report();
		Row e = r.createRow();
		e.setValue(0, "0");
		e.setValue(3, "3");
		r.add(e);
		e = r.createRow();
		e.setValue(1, "1");
		e.setValue(2, "2");
		r.add(e);

		r.load(new File("etc/le-assets.txt"));
		r.save("text.xlsx");
	}

	private void setStyleAttributes(String styleName, CellStyle style,
			Workbook wb) {
		XSSFCellStyle xstyle = (XSSFCellStyle) style;
		switch (styleName) {
		case STYLE_GOOD:
			// XSSFColor green = new XSSFColor(new byte[] { (byte) 198,
			// (byte) 239, (byte) 206 });
			// xstyle.setFillForegroundColor(green);
			// xstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			// XSSFFont f1 = (XSSFFont) wb.createFont();
			// f1.setColor(new XSSFColor(new byte[] { (byte) 0, (byte) 97,
			// (byte) 0 }));
			// xstyle.setFont(f1);
			break;
		case STYLE_BAD:
			// XSSFColor red = new XSSFColor(new byte[] { (byte) 255, (byte)
			// 199,
			// (byte) 206 });
			// xstyle.setFillForegroundColor(red);
			// xstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			// XSSFFont f2 = (XSSFFont) wb.createFont();
			// f2.setColor(new XSSFColor(new byte[] { (byte) 156, (byte) 0,
			// (byte) 6 }));
			// xstyle.setFont(f2);
			// break;
		case STYLE_HEADER:
			// XSSFColor header = new XSSFColor(new byte[] { (byte) 79,
			// (byte) 129, (byte) 189 });
			// xstyle.setFillForegroundColor(header);
			// xstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			// XSSFFont f3 = (XSSFFont) wb.createFont();
			// f3.setColor(IndexedColors.WHITE.index);
			// xstyle.setFont(f3);
			// break;
		case STYLE_NEUTRAL:
			// XSSFColor yellow = new XSSFColor(new byte[] { (byte) 255,
			// (byte) 235, (byte) 156 });
			// xstyle.setFillForegroundColor(yellow);
			// xstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			// XSSFFont f4 = (XSSFFont) wb.createFont();
			// f4.setColor(new XSSFColor(new byte[] { (byte) 156, (byte) 101,
			// (byte) 0 }));
			// xstyle.setFont(f4);
			// break;
		case STYLE_LIGHT_BLUE:
			// XSSFColor lightBlue = new XSSFColor(new byte[] { (byte) 75,
			// (byte) 172, (byte) 198 });
			// xstyle.setFillForegroundColor(lightBlue);
			// xstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			// XSSFFont f5 = (XSSFFont) wb.createFont();
			// f5.setColor(new XSSFColor(new byte[] { (byte) 255, (byte) 255,
			// (byte) 255 }));
			// xstyle.setFont(f5);
			// break;
		case STYLE_DARK_BLUE:
			// XSSFColor darkBlue = new XSSFColor(new byte[] { (byte) 79,
			// (byte) 129, (byte) 189 });
			// xstyle.setFillForegroundColor(darkBlue);
			// xstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			// XSSFFont f6 = (XSSFFont) wb.createFont();
			// f6.setColor(new XSSFColor(new byte[] { (byte) 255, (byte) 255,
			// (byte) 255 }));
			// xstyle.setFont(f6);
			// break;
		}
	}
}
